import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;
import java.util.Vector;



/*Desc: This program is used to removes stop words from the database,
 * Title, Desccriptin and comment. This program also performs stemming of 
 * terms.
 * 
 * */

public class remove_stop_words_db 
{
  Connection conn = null;
  Statement stmt=null;
  String dbname = "lsi";
  String username = "root";
  String password = "123";
  String old_table = "chromedb_simple";
  String new_table = "chromedb_cleaned";
  String stop_words_table = "stop_words";
  
  Vector <String> stop_words_vector = new Vector<String>();
  
  
  
  private void getInput()
  {
  try 
  	{
  	BufferedReader br =  new BufferedReader(new InputStreamReader(System.in));
  	
  	
  	System.out.println("Enter DB user (Default root ) ");
  	username = br.readLine();
  	System.out.println("Enter DB Password");
  	password = br.readLine();
  	
  	System.out.println("Enter database");
  	dbname = br.readLine();
  	
  	
  	
  	// String username = "root";//"Sangeeta";//"root";
  	// String paswd = "123";//"sangeeta@123456";//"123";
  	
  	} catch (IOException e) 
  	{ 	e.printStackTrace(); }
  	
  	
  }
  
  void process()
  {
	  
	  db_conn db = new db_conn();
	  conn=db.initdb(dbname, old_table, username, password);
	  String str1 = "create table  "+ new_table + " ( issueid int(11) Primary Key,title text, description text, comment text)";
	 System.out.println("str="+str1);

	  
	  try 	 
	     {
	      	stmt =  conn.createStatement();
		    stmt.executeUpdate(str1);
		   		    
    	} catch (SQLException e) 
    	{ e.printStackTrace(); }
    	
    	
    String del_str = "delete from  "+new_table;
    
    try 	 
    {
     	//stmt =  conn.createStatement();
	    stmt.executeUpdate(del_str);
	   		    
	} catch (SQLException e) 
	{ e.printStackTrace(); }
	
	
    	
    	String get_stop_words = "select * from  "+stop_words_table;
    	
    	try
    	{
    		
    		stmt.execute(get_stop_words);
    		ResultSet rs =  stmt.getResultSet();
    		while(rs.next())
    		{
    	        stop_words_vector.add(rs.getString("word"));
    	        
    		}
    		
    	}catch(SQLException e)
    	{ e.printStackTrace();}
    	
    	
    	System.out.println("Number of stop words="+ stop_words_vector.size());
    	
    	String get_data = "select * from  "+ old_table ;//+" limit 0,2";
    
    try {
		    stmt.execute(get_data);
		    ResultSet rs =  stmt.getResultSet();
		    
		    //1.read data from stop word list
		    //2. Read Data from db done
		    //3. run stemming and sw 
		    //4. remove words in the stop words
		    //5. fill the table
	
		    preprocess prob =  new preprocess();
		    while(rs.next())
		    {
		    	int issueid = rs.getInt("issueid");
		    	String title = rs.getString("title").toLowerCase().trim();
		    	String description = rs.getString("description").toLowerCase().trim();
		    	String comment = rs.getString("comments").toLowerCase().trim();
		    	
		    	title = prob.remove_SW_and_STEM(title);
		    	description =prob.remove_SW_and_STEM(description);
		    	comment =prob.remove_SW_and_STEM(comment);
		    	
		    	String title_arr[] = title.split(" ");
		    	String desc_arr[] = description.split(" ");
		    	String comment_arr[] = comment.split(" ");
		    	Vector<String> title_vector = new Vector<String>( Arrays.asList(title_arr));
		    	Vector<String> description_vector = new Vector<String>(Arrays.asList(desc_arr));
		    	Vector<String> comment_vector= new Vector<String>(Arrays.asList(comment_arr));
		    	
		    	for(int i=0;i<stop_words_vector.size();i++)
		    	{
		    		String remove_it = stop_words_vector.get(i);
		    		int tindex = title_vector.indexOf(remove_it);
		    		int dindex = description_vector.indexOf(remove_it);
		    		int cindex = comment_vector.indexOf(remove_it);
		    		
		    	
		    	     while(tindex!=-1)
		    		   {
		    		     title_vector.remove(tindex);
		    		     tindex = title_vector.indexOf(remove_it);
		    		     
		    		
		    		   }
		    		
		    		while(dindex!=-1)
		    		{description_vector.remove(dindex);
		    		System.out.println("val="+remove_it +"dindex="+dindex);
		    		dindex = description_vector.indexOf(remove_it);
		    		}
		    		
		    		while(cindex!=-1)
		    		{
		    		comment_vector.remove(cindex);
		    		cindex = comment_vector.indexOf(remove_it);
		    		}
		    	}//for stop words
		    	
		    	title="";
		    	description="";
		    	comment="";
		    	
		    	for(int i=0;i<title_vector.size();i++)
		    	{
		    		title=title+title_vector.get(i)+" ";
		    	}
		    	
		    	for(int i=0;i<description_vector.size();i++)
		    	{
		    		description=description+ description_vector.get(i)+" ";
		    	}
		    	for(int i=0;i<comment_vector.size();i++)
		    	{
		    		comment=comment+comment_vector.get(i)+" ";
		    	}
		    	
		    	
		    	String insertStr = "insert into  "+ new_table+ " values ("+issueid +",'"+title+"','"+
		    	 
		    	description+"','"+comment+"')";
		    	
		    	System.out.println("Insert Str="+insertStr);
		    	
		    	
		    	
		    	Statement stmt2 =conn.createStatement();
		    	stmt2.executeUpdate(insertStr);
		    	
		    }//
		    
		    
	
    } catch (SQLException e)
    { e.printStackTrace(); }
     
	  
	  
	  
	  
	  
  }
	
 public static void main(String args[])
 {
	 remove_stop_words_db rswd = new remove_stop_words_db();
	 rswd.getInput();
	 rswd.process();
	 
	 
 }
	
}
